create or replace package ac_diff is

  -- Author  : LEV ERUSALIMSKIY
  -- Created : 04/12/2011 8:56:50 AM
  -- Purpose : 
  
  -- Public type declarations
-- procedure app_params(pi_app_id1 number, pi_app_id2 number, pout_data in out ac_utils.res_type);

 procedure  app_diff_pages(pi_app_id1 number, pi_app_id2 number, p_res_id number, pi_page_id number);

 function Create_Header(p_app_id_1 varchar2, p_app_id_2 varchar2) return number;

 function app_diff_init(pi_app_id1 number, pi_app_id2 number) return number;

 -- function cr(p_str varchar2) return varchar2;

end ac_diff;
/
create or replace package body ac_diff is
-- forward definitions
 function  app_page_parameters(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_page_id number, p_sort number) return number;
 procedure app_params_Details(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number); 
 procedure app_comp(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_app_proc(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_appl_items(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_appl_lov(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_pages(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number);
 procedure app_auth_schemes(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_appl_authorization(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
--

 function cr(p_str varchar2) return varchar2 is
 begin
     return replace(replace(p_str, ac_utils.G_CRLF, ac_utils.G_CR), ac_utils.G_LF, ac_utils.G_CR);
 end cr;

 procedure prep_data(p_param_1_val in out clob,
                     p_param_2_val in out clob,
                     p_c_val_1 in out clob,
                     p_c_val_2 in out clob,
                     p_v_val_1 out varchar2,
                     p_v_val_2 out varchar2
                     ) as
  begin   
     p_c_val_1  := p_param_1_val;
     p_c_val_1  := p_param_2_val;
     p_v_val_1  := substr(p_c_val_1,1,4000);
     p_v_val_2  := substr(p_c_val_2,1,4000);
  end  prep_data;

 function Create_Header(p_app_id_1 varchar2, p_app_id_2 varchar2) return number is
   v_workspace_1 varchar2(500);
   v_workspace_2 varchar2(500);
   v_result_id   number;
   begin
     select app.workspace 
       into v_workspace_1
     from apex_applications app 
      where app.application_id = p_app_id_1;
      
     select app.workspace 
       into v_workspace_2
     from apex_applications app 
      where app.application_id = p_app_id_2;
      
     insert into  ac_results_header
      ( result_id, app_id_1, app_id_2, workspace_1, workspace_2)
     values
      (acc_seq.nextval, p_app_id_1, p_app_id_2, v_workspace_1, v_workspace_2  )
      returning result_id INTO v_result_id;
     
     return v_result_id; 
 end  Create_Header;

 function APP_REC(p_result_id number, p_app_id_1 varchar2, p_app_id_2 varchar2, p_sort number) return number is
   v_parent_id      number;
   v_sort           number;    
   begin
   
    insert into ac_results
     (result_id,    parent_id, param_name,  param_val_1, param_val_2, sort, is_branch,   object_type , is_src)
    values
     (p_result_id, null,       'Compare Applications', p_app_id_1,  p_app_id_2, p_sort, 'Y', ac_utils.G_APPLICATION, 'N' )
    returning id INTO v_parent_id;

    app_params_Details(p_result_id, v_parent_id, p_app_id_1,  p_app_id_2,    1); 

    app_auth_schemes  (p_result_id, v_parent_id, p_app_id_1,  p_app_id_2,    2);

    app_appl_authorization(p_result_id, v_parent_id, p_app_id_1, p_app_id_2, 3);

    app_appl_items    (p_result_id, v_parent_id, p_app_id_1,  p_app_id_2,    4);
     
    app_appl_lov      (p_result_id, v_parent_id,  p_app_id_1, p_app_id_2,    5);
     
    app_comp          (p_result_id, v_parent_id, p_app_id_1,  p_app_id_2,    6);
    
    app_app_proc      (p_result_id, v_parent_id, p_app_id_1,  p_app_id_2,    7);

    v_sort := 8;
    ac_diff_not_in.app_pages_not_in(p_result_id, v_parent_id, p_app_id_1, p_app_id_2, v_sort);
    ac_diff_not_in.app_pages_not_in(p_result_id, v_parent_id, p_app_id_2, p_app_id_1, v_sort);
    
    return v_parent_id;
 end  APP_REC;

 procedure app_params_Details(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id number;
 begin
 
     v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Application Properties', p_sort); 
 
     insert into ac_results
     (result_id,   parent_id,  param_name, param_val_1,   param_val_2, sort,
      help_text, is_branch,  is_src )
     select 
      p_result_id, v_parent_id, app1.u_name, app1.u_value, app2.u_value, p_sort,
      'APEX_APPLICATIONS.'||app1.u_name, 'Y', 'Y'
     from 
     (
      select * from vac_application where application_id = pi_app_id1
     ) app1 
     join
     (
      select * from vac_application where application_id = pi_app_id2
     ) app2 ON
     ( 
      (app1.U_NAME = app2.U_NAME) and
      ( -- select not equal values. ! here  null = null
        (    (app1.u_value is not null or app2.u_value is not null) 
         and lnnvl(app1.u_value=app2.u_value)
        )
      )
     );

 end app_params_Details;

 procedure app_appl_items(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
  v_parent_id_0    number;
  v_parent_id_1    number;
  v_curr_item_name varchar2(100)  := '##?###';
  v_title          varchar2(1000) := 'Appl Items';
  begin

   v_parent_id_0 := ac_utils.ins_result_msg(p_result_id,  pi_parent_id,  v_title, p_sort, true, ac_utils.G_APPLICATION_ITEM);

   ac_diff_not_in.appl_items_not_in(p_result_id, v_parent_id_0, pi_app_id1, pi_app_id2);   
   ac_diff_not_in.appl_items_not_in(p_result_id, v_parent_id_0, pi_app_id2, pi_app_id1);   

   for r in (
                with p1 as 
                ( select ai.item_name,  ai.U_NAME, ai.U_VALUE,
                         ai.src_view_name 
                  from vac_application_item ai
                  where ai.application_id = pi_app_id1
                ),
                 p2 as 
                ( select ai.item_name,  ai.U_NAME , ai.U_VALUE 
                  from vac_application_item ai
                  where ai.application_id = pi_app_id2
                )
                select
                 p_result_id, v_parent_id_0, 
                 p1.item_name, p1.U_NAME, p1.src_view_name,
                 p1.U_VALUE U_value_1, p2.u_value  U_value_2, p_sort
                from p1 join p2 
                     ON
                     (    (p1.item_name = p2.item_name) 
                      and (p1.u_name = p2.u_name) 
                      and ((p1.u_value is not null or p2.u_value is not null) 
                            and lnnvl(p1.u_value=p2.u_value)
                          )
                     ) 
                 order by p1.item_name
            ) loop
        if (r.item_name != v_curr_item_name) then
         v_parent_id_1    := ac_utils.ins_result_msg(p_result_id,  v_parent_id_0, v_title||':'||r.item_name, 3, true, ac_utils.G_APPLICATION_ITEM );
         v_curr_item_name := r.item_name;
        end if;    
            
       insert into ac_results
       (result_id,   parent_id,     param_name, param_val_1, param_val_2, sort,
       help_text, is_branch, is_src)
       values
       (p_result_id, v_parent_id_1, r.u_name,     r.u_value_1, r.u_value_2, 1 ,
        r.src_view_name||r.u_name,'N','N' );
              
    end loop;
  
 end app_appl_items;

 procedure app_appl_lov(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id_0    number;
 v_parent_id_1    number;
 v_curr_lov_name  varchar2(100) := '##?###';
 v_title          varchar2(1000) := 'LOVS';

 begin
    v_parent_id_0 := ac_utils.ins_result_msg(p_result_id,  pi_parent_id,  v_title, 1, true, ac_utils.G_LOVS);
 
    ac_diff_not_in.app_appl_lov_not_in(p_result_id, v_parent_id_0, pi_app_id1, pi_app_id2);
    ac_diff_not_in.app_appl_lov_not_in(p_result_id, v_parent_id_0, pi_app_id2, pi_app_id1);
     
    for r in (
      with p1 as 
      ( select lv.list_of_values_name, lv.lov_id, lv.referenced_lov_id,  lv.U_NAME, lv.U_VALUE 
        from vac_lov lv
        where lv.application_id = pi_app_id1
      ),
       p2 as 
      ( select lv.list_of_values_name, lv.lov_id, lv.referenced_lov_id,  lv.U_NAME, lv.U_VALUE 
        from vac_lov lv
        where lv.application_id = pi_app_id2
      )
      select
       --p_result_id, v_parent_id, 
       p1.list_of_values_name, p1.U_NAME, p1.U_VALUE U_value_1, p2.u_value  U_value_2 --, p_sort
      from p1 join p2 
           ON
           (    (p1.list_of_values_name = p2.list_of_values_name) 
            and (p1.u_name = p2.u_name) 
            and (nvl(p1.u_value,'#$#$#$#') != nvl(p2.u_value,'#$#$#$#'))   
           ) 
       order by p1.list_of_values_name

    ) loop
       if (r.list_of_values_name != v_curr_lov_name) then
         v_parent_id_1      := ac_utils.ins_result_msg(p_result_id,  v_parent_id_0, 'LOV: '||r.list_of_values_name, 1 , true, ac_utils.G_LOVS);
         v_curr_lov_name    := r.list_of_values_name;
       end if; 
       
      insert into ac_results
      (result_id,   parent_id,     param_name, param_val_1, param_val_2 , sort, 
       help_text, is_branch, is_src)
      values
      (p_result_id, v_parent_id_1, r.U_NAME,  r.U_value_1, r.U_value_2,     1, 
       'APEX_APPLICATION_LOVS.'||r.U_NAME,'N','N'   );
             
    end loop;
       
 end app_appl_lov;

 procedure app_page_item_parameters(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_page_id number, p_sort number) as
   v_parent_id      number;
   v_parent_id_0    number;
   v_curr_item_name varchar2(100);
   begin
 
     v_parent_id_0 := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Items', 1, true, ac_utils.G_PAGE_ITEMS);
  
     for r in ( 
      with p1 as 
      ( select pi1.page_id, pi1.item_name,  pi1.U_NAME , pi1.U_VALUE 
        from vac_page_item pi1
        where pi1.application_id = pi_app_id1
         and pi1.page_id = p_page_id
      ),
       p2 as 
      ( select pi1.page_id, pi1.item_name,  pi1.U_NAME , pi1.U_VALUE 
        from vac_page_item pi1
        where pi1.application_id = pi_app_id2
         and pi1.page_id = p_page_id
      )
      select
       p_result_id, v_parent_id, p1.item_name, p1.U_NAME, p1.U_VALUE U_value_1, p2.u_value  U_value_2, p_sort
      from p1 join p2 
           ON
           (    (p1.item_name = p2.item_name) 
            and (p1.u_name = p2.u_name) 
            and (nvl(p1.u_value,'#$#$#$#') != nvl(p2.u_value,'#$#$#$#'))   
           ) 
       order by p1.item_name
       ) loop
           
        if (nvl(v_curr_item_name,'#$#$#$#') !=  r.item_name) then
         v_parent_id      := ac_utils.ins_result_msg(p_result_id,  v_parent_id_0, 'Item: '||r.item_name, 1 , true, ac_utils.G_PAGE_ITEMS);
         v_curr_item_name := r.item_name;
        end if;  
        insert into ac_results
        (result_id,   parent_id,   param_name, param_val_1, param_val_2 , sort,
         help_text, is_branch, is_src)
        values
        (p_result_id, v_parent_id, r.U_NAME,  r.U_value_1, r.U_value_2,     1,
         'APEX_APPLICATION_PAGE_ITEMS.'||r.u_name, 'N', 'N');
           
      end loop;
   end app_page_item_parameters;

 function app_page_items(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_page_id number, p_sort number) return number  is
 v_sort      number := p_sort;
 begin
    ac_diff_not_in.app_page_items_not_in   (p_result_id, pi_parent_id, pi_app_id1, pi_app_id2, p_page_id);
    ac_diff_not_in.app_page_items_not_in   (p_result_id, pi_parent_id, pi_app_id2, pi_app_id1, p_page_id);
    app_page_item_parameters(p_result_id, pi_parent_id, pi_app_id1, pi_app_id2, p_page_id, v_sort);
    
    return v_sort;
 end  app_page_items;

 function app_page_parameters(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_page_id number, p_sort number) return number is
 v_sort number := p_sort;
 v_src_1          clob;
 v_src_2          clob;
 v_is_src         varchar2(1);
 v_value_1        varchar2(4000);
 v_value_2        varchar2(4000);
 v_param_name     varchar2(4000); 
 begin

    for r in ( 
                with app_page1 as (
               select application_id, page_id, u_name, u_value,
                      help_text_sr,   page_html_header_sr, 
                      javascript_code_onload_sr, 
                      src_view_name
                from vac_page ap1 
                where ap1.application_id = pi_app_id1
                  and ap1.page_id = p_page_id
                ),
                 app_page2 as (
               select application_id, page_id, u_name, u_value,
                      help_text_sr,   page_html_header_sr, 
                      javascript_code_onload_sr  
                from vac_page ap1 
                where ap1.application_id = pi_app_id2
                  and ap1.page_id = p_page_id
                )
                select p1.page_id,            p1.u_name, 
                       p1.u_value u_value_1,  p2.u_value u_value_2,
                       p1.help_text_sr        help_text_sr_1,     
                       p1.page_html_header_sr page_html_header_sr_1, 
                       p1.javascript_code_onload_sr javascript_code_onload_sr_1,
                       p2.help_text_sr        help_text_sr_2,     
                       p2.page_html_header_sr page_html_header_sr_2, 
                       p2.javascript_code_onload_sr javascript_code_onload_sr_2,
                       p1.src_view_name
                from app_page1 p1 join app_page2 p2
                 ON (  
                       (p1.u_name = p2.u_name) 
                   and (nvl(p1.u_value,'#$#$#$#') != nvl(p2.u_value,'#$#$#$#'))   
                   )
                 order by p1.u_name
             ) LOOP
              v_src_1 := null;
              v_src_2 := null;
              if upper(r.u_name) = 'HELP_TEXT_HS' then
                 prep_data( 
                   r.help_text_sr_1, r.help_text_sr_2,
                   v_src_1,   v_src_2,
                   v_value_1, v_value_2);
                 v_param_name := 'HELP_TEXT';
                 v_is_src  := 'Y';
              elsif upper(r.u_name) = 'PAGE_HTML_HEADER_HS' then
                 prep_data( 
                   r.page_html_header_sr_1, r.page_html_header_sr_2,
                   v_src_1,   v_src_2,
                   v_value_1, v_value_2);
                 v_param_name := 'PAGE_HTML_HEADER';
                 v_is_src  := 'Y';
              elsif upper(r.u_name) = 'JAVASCRIPT_CODE_ONLOAD_HS' then
                 prep_data( 
                   r.javascript_code_onload_sr_1, r.javascript_code_onload_sr_2,
                   v_src_1,   v_src_2,
                   v_value_1, v_value_2);
                 v_param_name := 'JAVASCRIPT_CODE_ONLOAD';
                 v_is_src  := 'Y';
               else
                 v_is_src  := 'N';
                 v_value_1 := r.U_value_1;
                 v_value_2 := r.U_value_2;
                 v_param_name := upper(r.u_name);
               end if;
    
       insert into ac_results
          (result_id,   parent_id,   param_name, param_val_1, param_val_2, object_type,
           help_text,    sort,       
           src_val_1,   src_val_2,   is_branch,  is_src)
       values
          (p_result_id, pi_parent_id, v_param_name,   v_value_1,   v_value_2, ac_utils.G_PAGE_ITEMS, 
           'APEX_APPLICATION_PAGES.'||r.u_name,       v_sort,
           v_src_1, v_src_2,         'N', 'N');
       v_sort := v_sort + 1;    
     end loop;
   
     return v_sort; 
 end  app_page_parameters;

 function is_report(pi_app_id number, pi_page_id number, pi_region_id number) return boolean is
   v_res boolean;
   v_tmp       number;
   begin
     begin
     select r1.region_id into v_tmp 
                  from APEX_APPLICATION_PAGE_RPT r1 
                  where r1.application_id = pi_app_id
                    and r1.page_id   = pi_page_id  
                    and r1.region_id = pi_region_id;
       v_res := true;
     exception when NO_DATA_FOUND then
       v_res := false;
     end;
     return v_res;
   end is_report;

 procedure app_report_cols(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, pi_region_id1 number, pi_region_id2 number) is
   v_parent_id      number;
   v_parent_id_cols number;
   v_column_alias   varchar2(100) := '*****';
   v_title          varchar2(100) := 'Report columns';
   
   v_src_1         clob;
   v_src_2         clob;
   v_is_src        varchar2(1) := 'N';
   v_value_1       varchar2(4000);
   v_value_2       varchar2(4000);
   v_param_name    varchar2(4000);
   
   begin

     v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_title, 3, true, ac_utils.G_REPORT_COLUMN);
     ac_diff_not_in.app_report_cols_not_in(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id, pi_region_id1, pi_region_id2);
     ac_diff_not_in.app_report_cols_not_in(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id, pi_region_id2, pi_region_id1);
       
     for r in ( with app_r1 as (
                                select application_id, page_id, region_name, region_id, r1.column_alias, 
                                       primary_key_column_source_sr, u_name, u_value 
                                from vac_page_rpt_cols r1 
                                where r1.application_id = pi_app_id1
                                  and r1.page_id   = pi_page_id
                                  and r1.region_id = pi_region_id1
                                ),
                               app_r2 as (
                                select application_id, page_id, region_name, region_id, r2.column_alias, 
                                       primary_key_column_source_sr, u_name, u_value,
                                       r2.src_view_name
                                  from vac_page_rpt_cols r2 
                                 where r2.application_id = pi_app_id2
                                   and r2.page_id   = pi_page_id  
                                   and r2.region_id = pi_region_id2
                                )
                                  select r1.page_id,              r1.column_alias, r1.u_name, 
                                         r1.region_id region_id1, r2.region_id region_id2,
                                         r1.u_value u_value_1,    r2.u_value u_value_2,
                                         r1.primary_key_column_source_sr primary_key_column_source_sr_1,
                                         r2.primary_key_column_source_sr primary_key_column_source_sr_2,
                                         r2.src_view_name
                                  from app_r1 r1 join app_r2 r2
                                   ON (  
                                         (r1.u_name  = r2.u_name) 
                                     and (r1.column_alias = r2.column_alias)
                                     and (nvl(r1.u_value,'#$#$#$#') != nvl(r2.u_value,'#$#$#$#'))   
                                     )
                                   order by r1.column_alias
                  ) loop
             if r.column_alias != v_column_alias then 
                 v_parent_id_cols := ac_utils.ins_result_msg(p_result_id,  v_parent_id, 'Column:'||r.column_alias, 3, true, ac_utils.G_REPORT_COLUMN);
                 v_column_alias   := r.column_alias;
             end if;

             if upper(r.u_name) = 'PRIMARY_KEY_COLUMN_SOURCE_HS' then
               v_src_1  := r.primary_key_column_source_sr_1;
               v_src_2  := r.primary_key_column_source_sr_2;
               v_value_1:= substr(v_src_1,1,4000);
               v_value_2:= substr(v_src_2,1,4000);
               v_param_name := 'PRIMARY_KEY_COLUMN_SOURCE';
               v_is_src := 'Y';
             else
               v_src_1   := null;
               v_src_2   := null;
               v_is_src  := 'N';
               v_value_1 := r.U_value_1;
               v_value_2 := r.U_value_2;
               v_param_name := upper(r.u_name);
             end if;

            insert into ac_results
            (result_id,   parent_id,        param_name,   param_val_1, param_val_2 , sort,
            help_text, is_branch, is_src)
            values
            (p_result_id, v_parent_id_cols, v_param_name, v_value_1,   v_value_2, 1,
             r.src_view_name || r.u_name,'N','N'   );
              
        end loop;
 end  app_report_cols;

 procedure app_report_print_params(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, pi_region_id1 number, pi_region_id2 number) is
 v_parent_id number;
 begin
       v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Report Print Parameters', 1, true, ac_utils.G_REPORT_PRINT_PARAMETER);
       for r in (  with app_r1 as (
                    select application_id, page_id, region_name, region_id, u_name, u_value 
                    from vac_page_rpt r1 
                    where r1.application_id = pi_app_id1
                      and r1.page_id   = pi_page_id  
                      and r1.region_id = pi_region_id1
                    ),
                   app_r2 as (
                    select application_id, page_id, region_name, region_id, u_name, u_value 
                      from vac_page_rpt r2 
                     where r2.application_id = pi_app_id2
                       and r2.page_id   = pi_page_id  
                       and r2.region_id = pi_region_id2
                    )
                      select r1.page_id,              r1.u_name, 
                             r1.region_id region_id1, r2.region_id region_id2,
                             r1.u_value u_value_1,    r2.u_value u_value_2
                      from app_r1 r1 join app_r2 r2
                       ON (  
                             (r1.u_name  = r2.u_name) 
                         and (nvl(r1.u_value,'#$#$#$#') != nvl(r2.u_value,'#$#$#$#'))   
                         )       
                 ) loop
            insert into ac_results
            (result_id,   parent_id,   param_name, param_val_1, param_val_2 , sort,
            help_text, is_branch, is_src)
            values
            (p_result_id, v_parent_id, r.U_NAME,  r.U_value_1, r.U_value_2, 1   ,
            'APEX_APPLICATION_PAGE_RPT.'||r.u_name,'N','N');
       end loop;
 end app_report_print_params;

 function app_page_branches(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) return number  is
 v_parent_id   number; 
 v_parent_id_1 number; 
 v_branch_sig  varchar2(100) := '******';
 v_title       varchar2(1000) := 'Branches';
 v_sort        number := p_sort + 1;
 begin
    
    v_parent_id   := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_title, 1, true, ac_utils.G_BRANCH);
    ac_diff_not_in.app_page_branches_not_in(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id);
    ac_diff_not_in.app_page_branches_not_in(p_result_id, v_parent_id, pi_app_id2, pi_app_id1, pi_page_id);

    for r in (with b1 as (
                    select 
                     branch_point||':'||process_sequence br_sig,
                     branch_point, process_sequence, 
                     u_value, u_name
                    from  vac_branch b 
                     where b.application_id = pi_app_id1
                      and  b.page_id = pi_page_id
                    ),
                     b2 as (
                    select 
                     branch_point||':'|| process_sequence br_sig, 
                     branch_point, process_sequence, 
                     u_value, u_name
                    from  vac_branch b 
                     where b.application_id = pi_app_id2
                      and  b.page_id = pi_page_id
                    )              
                    select b1.br_sig, b1.u_name, b1.u_value u_value_1,  b2.u_value u_value_2  
                    from b1 join b2 ON 
                    (    (b1.branch_point = b2.branch_point) 
                     and (b1.process_sequence = b2.process_sequence)
                     and (b1.u_name = b2.u_name)
                     and (nvl(b1.u_value,'#$#$#$#') != nvl(b2.u_value,'#$#$#$#'))
                    )
                    order by  b1.br_sig
              ) loop

        if (v_branch_sig != r.br_sig) then
            v_parent_id_1 := ac_utils.ins_result_msg(p_result_id,  v_parent_id, v_title||' '||r.br_sig, 1, true, ac_utils.G_BRANCH);
            v_branch_sig  := r.br_sig;
        end if;

        insert into ac_results
        (result_id,   parent_id,     param_name, param_val_1, param_val_2 , sort , help_text, 
         is_branch, is_src)
          values
        (p_result_id, v_parent_id_1, r.U_NAME,     r.U_value_1, r.U_value_2, 1,  
         'APEX_APPLICATION_PAGE_BRANCHES.'||r.u_name, 
         'N', 'N' );

      end loop;
      
      return v_sort; 
  end app_page_branches;  
 
-- This is report print parameters 
 procedure app_region_rpt(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, pi_region_id1 number, pi_region_id2 number) is
   v_parent_id     number;
   v_parent_id_tmp number;
   v_is_report_app1 boolean := is_report(pi_app_id1, pi_page_id, pi_region_id1);
   v_is_report_app2 boolean := is_report(pi_app_id2, pi_page_id, pi_region_id2);
   
   v_msg         varchar2(1000);
   v_title       varchar2(1000) := 'Report';
 begin
     if (v_is_report_app1 or v_is_report_app2) then
       v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, ac_utils.G_REPORT, 1, true, ac_utils.G_REPORT);
     end if;
     
     if v_is_report_app1 and (not v_is_report_app2) then
      v_msg           := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
      v_parent_id_tmp := ac_utils.ins_result_msg(p_result_id,  v_parent_id, v_msg, 1);
     elsif v_is_report_app2 and (not v_is_report_app1) then
      v_msg           := ac_utils.in1NotIn2(v_title, pi_app_id2, pi_app_id1);
      v_parent_id_tmp := ac_utils.ins_result_msg(p_result_id,  v_parent_id, v_msg, 1);
     elsif (v_is_report_app1 and v_is_report_app2) then
      app_report_print_params(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id, pi_region_id1, pi_region_id2);     
      app_report_cols        (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id, pi_region_id1, pi_region_id2);    
    end if; -- if v_is_report_app1 and (not v_is_report_app2) then
 end  app_region_rpt;

 function app_regions(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) return number is
 v_parent_id     number;
 v_parent_id_reg number;
 v_sort          number := p_sort;
 v_src_1         clob;
 v_src_2         clob;
 v_is_src        varchar2(1);
 v_value_1       varchar2(4000);
 v_value_2       varchar2(4000);
 v_param_name    varchar2(4000);
 begin
     v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Regions', v_sort, true, ac_utils.G_REGION);

     ac_diff_not_in.app_regions_not_in(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id);
     ac_diff_not_in.app_regions_not_in(p_result_id, v_parent_id, pi_app_id2, pi_app_id1, pi_page_id);
     
     for r in (select r1.region_name, r1.region_id region_id1, 
                      r2.region_id  region_id2
                 from  APEX_APPLICATION_PAGE_REGIONS r1 
                  join APEX_APPLICATION_PAGE_REGIONS r2 
                 ON (r1.region_name = r2.region_name) and (r1.page_id = r2.page_id)
                 where r1.application_id = pi_app_id1
                  and  r2.application_id = pi_app_id2
                  and  r1.page_id =  pi_page_id
              ) loop
       v_parent_id_reg := ac_utils.ins_result_msg(p_result_id,  v_parent_id, 'Region:'|| r.region_name, 1 , true, ac_utils.G_REGION );
       
       for rec in ( 
               with app_r1 as (
               select application_id, page_id, region_name, region_id, 
                      region_source_sr, u_name, u_value 
                from vac_page_regions r1 
                where r1.application_id = pi_app_id1
                  and r1.page_id = pi_page_id   
                  and r1.region_name = r.region_name
                ),
                 app_r2 as (
               select application_id,   page_id, region_name, region_id, 
                      region_source_sr, u_name,  u_value 
                from vac_page_regions r2 
                where r2.application_id = pi_app_id2
                  and r2.page_id = pi_page_id   
                  and r2.region_name = r.region_name
                )
                select r1.page_id,              r1.u_name, 
                       r1.region_id region_id1, r2.region_id region_id2,
                       r1.region_source_sr region_source_sr_1,
                       r2.region_source_sr region_source_sr_2,
                       r1.u_value u_value_1,    r2.u_value u_value_2
                from app_r1 r1 join app_r2 r2
                 ON (  
                       (r1.u_name  = r2.u_name) 
                   and (nvl(r1.u_value,'#$#$#$#') != nvl(r2.u_value,'#$#$#$#'))   
                   )
                  ) loop
             if (rec.U_NAME = 'REGION_SOURCE_HS') then 
               v_src_1   := rec.REGION_SOURCE_sr_1;
               v_src_2   := rec.REGION_SOURCE_sr_2;
               v_value_1 := substr(v_src_1,1,4000);
               v_value_2 := substr(v_src_2,1,4000);
               v_is_src  := 'Y';
               v_param_name := 'REGION_SOURCE';
             else
               v_is_src  := 'N';
               v_value_1 := rec.U_value_1;
               v_value_2 := rec.U_value_2;
               v_param_name := upper(rec.u_name);
             end if;                  
                  
          insert into ac_results
          (result_id,   parent_id,       param_name,    param_val_1, param_val_2, 
           sort,     help_text, is_branch, is_src)
          values
          (p_result_id, v_parent_id_reg, v_param_name,  v_value_1,   v_value_2, 1, 
           'APEX_APPLICATION_PAGE_REGIONS.'||v_param_name,'N','N' );
       end loop;
       
       app_region_rpt(p_result_id, v_parent_id_reg, pi_app_id1, pi_app_id2, pi_page_id, r.region_id1, r.region_id2);
       
     end loop;   
  
  return   v_sort;
 end app_regions;

 function app_ir_region_cols(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) return number is
   v_parent_id        number;
   v_parent_id_1      number;
   v_sort             number := p_sort;
   v_cur_column_alias varchar2(4000) := '*****';
   begin
     v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Interactive Report Columns', v_sort, true, ac_utils.G_INTERACTIVE_REPORT_COLS);

     ac_diff_not_in.app_report_ir_col_not_in(p_result_id,  v_parent_id, pi_app_id1, pi_app_id2, pi_page_id);
     ac_diff_not_in.app_report_ir_col_not_in(p_result_id,  v_parent_id, pi_app_id2, pi_app_id1, pi_page_id);
     
     for r in (with rc1 as
               ( select page_id, u_name, nvl(u_value,'*****') u_value, 
                        column_alias
                 from vac_app_page_ir_col irc1
                 where irc1.application_id = pi_app_id1
                   and irc1.page_id = pi_page_id
               ),
               rc2 as
               (
                 select page_id, u_name, nvl(u_value,'*****') u_value, 
                        column_alias, src_view_name
                 from vac_app_page_ir_col irc1
                 where irc1.application_id = pi_app_id2
                   and irc1.page_id = pi_page_id
               )
               select rc1.column_alias, rc1.u_name, 
                      rc1.u_value u_value_1,rc2.u_value u_value_2, 
                      rc2.src_view_name  
                from rc1 join rc2 ON (  (rc1.u_name = rc2.u_name) 
                                    and (rc1.column_alias = rc2.column_alias)
                                    and (rc1.u_value != rc2.u_value)
                                     )
               order by rc1.column_alias
              ) loop
        if (r.column_alias !=  v_cur_column_alias) then
          v_parent_id_1 := ac_utils.ins_result_msg(p_result_id,  v_parent_id, r.column_alias, 1, true, ac_utils.G_INTERACTIVE_REPORT_COLS);
          v_cur_column_alias := r.column_alias;
        end if;
                          
        insert into ac_results
        (result_id,   parent_id,     param_name, param_val_1, param_val_2, 
           sort,     help_text, is_branch, is_src)
          values
        (p_result_id, v_parent_id_1, r.u_name,   r.u_value_1, r.u_value_2, 
           1, r.src_view_name||'.'||r.column_alias,'N','N' );
     end loop;
     
     return p_sort + 1;
 end  app_ir_region_cols;

 function app_ir_regions(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) return number is
 v_parent_id     number;
 v_parent_id_reg number;
 v_sort          number := p_sort;
 v_src_1         clob;
 v_src_2         clob;
 v_is_src        varchar2(1);
 v_value_1       varchar2(4000);
 v_value_2       varchar2(4000);
 v_param_name    varchar2(4000);
 begin
     v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Interactive Report', v_sort, true, ac_utils.G_INTERACTIVE_REPORT);

     ac_diff_not_in.app_page_report_ir_not_in(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id);
     ac_diff_not_in.app_page_report_ir_not_in(p_result_id, v_parent_id, pi_app_id2, pi_app_id1, pi_page_id);

     for r in (select r1.region_name
                 from  apex_application_page_ir r1 
                  join apex_application_page_ir r2 
                    ON (r1.region_name = r2.region_name) 
                   and (r1.page_id = r2.page_id)
                 where r1.application_id = pi_app_id1
                  and  r2.application_id = pi_app_id2
                  and  r1.page_id =  pi_page_id
              ) loop
       v_parent_id_reg := ac_utils.ins_result_msg(p_result_id,  v_parent_id, 'IR Region:'|| r.region_name, 1, 
                                                  true, ac_utils.G_INTERACTIVE_REPORT );
       for rec in ( 
               with app_r1 as (
               select application_id, page_id, region_name, region_id, 
                      u_name,         u_value, sql_query,   src_view_name 
                from vac_app_page_ir r1 
                where r1.application_id = pi_app_id1
                  and r1.page_id = pi_page_id   
                  and r1.region_name = r.region_name
                ),
                 app_r2 as (
               select application_id, page_id, region_name, region_id, 
                      u_name,         u_value, sql_query 
                from vac_app_page_ir r2 
                where r2.application_id = pi_app_id2
                  and r2.page_id = pi_page_id   
                  and r2.region_name = r.region_name
                )
                select r1.page_id,               r1.u_name, 
                       r1.sql_query sql_query_1, r2.sql_query sql_query_2,
                       r1.u_value u_value_1,     r2.u_value u_value_2,
                       r1.src_view_name
                from app_r1 r1 join app_r2 r2
                 ON (  
                       (r1.u_name  = r2.u_name) 
                   and (nvl(r1.u_value,'#$#$#$#') != nvl(r2.u_value,'#$#$#$#'))   
                   )
                  ) loop
             if (rec.U_NAME = 'SQL_QUERY_HS') then 
               v_src_1   := rec.sql_query_1;
               v_src_2   := rec.sql_query_2;
               v_value_1 := substr(v_src_1,1,4000);
               v_value_2 := substr(v_src_2,1,4000);
               v_is_src  := 'Y';
               v_param_name := 'SQL_QUERY';
             else
               v_is_src  := 'N';
               v_value_1 := rec.U_value_1;
               v_value_2 := rec.U_value_2;
               v_param_name := upper(rec.u_name);
             end if;                  
                  
          insert into ac_results
          (result_id,   parent_id,       param_name,    param_val_1, param_val_2, 
           sort,     help_text, is_branch, is_src)
          values
          (p_result_id, v_parent_id_reg, v_param_name,  v_value_1,   v_value_2, 
           1, rec.src_view_name||'.'||v_param_name,'N','N' );
       end loop;
     end loop;   
     
     v_sort := app_ir_region_cols(p_result_id, v_parent_id_reg, pi_app_id1, pi_app_id2, pi_page_id, p_sort);
     
  return v_sort;   
 end app_ir_regions;
  
 function app_page_buttons(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) return number is
 v_parent_id   number; 
 v_parent_id_button number;
 v_button_name varchar2(100) := '***##***';
 v_sort        number := p_sort+1;
 begin
     ac_diff_not_in.app_page_buttons_not_in(p_result_id, pi_parent_id, pi_app_id1, pi_app_id2, pi_page_id);      
     ac_diff_not_in.app_page_buttons_not_in(p_result_id, pi_parent_id, pi_app_id2, pi_app_id1, pi_page_id);      
     
     for r in (   with app_b1 as (
                    select application_id, page_id, u_name, u_value , button_name
                    from vac_page_buttons b1 
                    where b1.application_id = pi_app_id1
                      and b1.page_id   = pi_page_id  
                    ),
                   app_b2 as (
                    select application_id, page_id, u_name, u_value , button_name
                      from vac_page_buttons b2 
                     where b2.application_id = pi_app_id2
                       and b2.page_id   = pi_page_id  
                    )
                      select ab1.page_id,              ab1.u_name, ab1.button_name,
                             ab1.u_value u_value_1,    ab2.u_value u_value_2
                      from app_b1 ab1 join app_b2 ab2
                       ON (  
                             (ab1.u_name  = ab2.u_name) 
                         and (ab1.button_name = ab2.button_name)
                         and (nvl(ab1.u_value,'#$#$#$#') != nvl(ab2.u_value,'#$#$#$#'))   
                         )
                   order by ab1.button_name   
              ) loop
       if (r.button_name != v_button_name) then
         v_parent_id_button := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Button: '||r.button_name, 1, true, ac_utils.G_BUTTON);
         v_button_name := r.button_name;
       end if;
       insert into ac_results
        (result_id,      parent_id,     param_name,  param_val_1, param_val_2 , sort,
        help_text, is_branch, is_src)
       values
        (p_result_id,    v_parent_id_button, r.u_name, r.u_value_1, r.u_value_2,  1,
        'APEX_APPLICATION_PAGE_BUTTONS.'||r.u_name,'N','N');
     end loop; 
     return  v_sort;     
 end app_page_buttons;
 
 function app_page_validations(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) return number is
 v_parent_id_0    number;
 v_parent_id_1    number;
 v_val_name       varchar2(100) := '******';
 v_title          varchar2(1000) := ac_utils.G_VALIDATIONS;
 v_sort           number := p_sort + 1;
 begin
    v_parent_id_0 := ac_utils.ins_result_msg(p_result_id,  pi_parent_id,  v_title, 1, true, ac_utils.G_VALIDATIONS);
    ac_diff_not_in.app_page_validations_not_in(p_result_id, pi_parent_id, pi_app_id1, pi_app_id2, pi_page_id); 
    ac_diff_not_in.app_page_validations_not_in(p_result_id, pi_parent_id, pi_app_id2, pi_app_id1, pi_page_id); 
    
    for r in ( with v1 as (
                    select 
                     v.validation_name,
                     u_value, u_name,
                     src_view_name
                    from  vac_val v 
                     where v.application_id = pi_app_id1
                      and  v.page_id = pi_page_id
                    ),
                     v2 as (
                    select 
                     v.validation_name,
                     u_value, u_name
                    from  vac_val v 
                     where v.application_id = pi_app_id2
                      and  v.page_id = pi_page_id
                    )              
                    select v1.validation_name,   v1.u_name, 
                           v1.u_value u_value_1, v2.u_value u_value_2, 
                           v1.src_view_name  
                    from v1 join v2 ON 
                    (    (v1.validation_name = v2.validation_name) 
                     and (v1.u_name = v2.u_name)
                     and (nvl(v1.u_value,'#$#$#$#') != nvl(v2.u_value,'#$#$#$#'))
                    )
                    order by  v1.validation_name
             ) loop
        if (v_val_name != r.validation_name) then
            v_parent_id_1 := ac_utils.ins_result_msg(p_result_id,  v_parent_id_0, r.validation_name, 1, true, ac_utils.G_VALIDATIONS );
            v_val_name := r.validation_name;
        end if;

        insert into ac_results
        (result_id,   parent_id,     param_name, param_val_1, param_val_2 , sort , help_text, is_branch, is_src)
        values
        (p_result_id, v_parent_id_1, r.U_NAME,     r.U_value_1, r.U_value_2, 1,      r.src_view_name||'.'||r.u_name,
        'N','N' );
    end loop;
    
    return v_sort;
 end  app_page_validations; 
 
 procedure app_appl_authorization(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id_0    number;
 v_parent_id_1    number;
 v_title          varchar2(1000) := ac_utils.G_AUTHORIZATION; 
 v_auth_name      varchar2(1000) := '*****';
 begin
    v_parent_id_0 := ac_utils.ins_result_msg(p_result_id,  pi_parent_id,  v_title, 1, true, ac_utils.G_AUTHORIZATION);
    ac_diff_not_in.app_appl_authorization_not_in(p_result_id, v_parent_id_0, pi_app_id1, pi_app_id2);
    ac_diff_not_in.app_appl_authorization_not_in(p_result_id, v_parent_id_0, pi_app_id2, pi_app_id1);

    for r in (with auth1 as
               (select authorization_scheme_name, src_view_name,
                       u_name, u_value
                from vac_application_authorization auth1
                where auth1.application_id = pi_app_id1
               ),
              auth2 as
               (select authorization_scheme_name, 
                       u_name, u_value
                from vac_application_authorization auth1
                where auth1.application_id = pi_app_id2
               )
              select auth1.authorization_scheme_name, auth1.u_name, 
                     auth1.u_value U_value_1, auth2.u_value U_value_2,
                     auth1.src_view_name
              from auth1 join auth2 ON (auth1.u_name = auth2.u_name) and
                (auth1.authorization_scheme_name = auth2.authorization_scheme_name) and
                ( -- select not equal values. ! here  null = null
                  (    (auth1.u_value is not null or auth2.u_value is not null) 
                   and lnnvl(auth1.u_value = auth2.u_value)
                  )
                )
              order by auth1.authorization_scheme_name
              ) loop
          if (r.authorization_scheme_name != v_auth_name) then
            v_parent_id_1 := ac_utils.ins_result_msg(p_result_id,  v_parent_id_0, r.authorization_scheme_name, 1, true, ac_utils.G_AUTHORIZATION );
            v_auth_name := r.authorization_scheme_name; 
          end if;    
              
          insert into ac_results
          (result_id,   parent_id,     param_name, param_val_1, param_val_2 , sort , help_text, 
           is_branch, is_src)
          values
          (p_result_id, v_parent_id_1, r.U_NAME,   r.U_value_1, r.U_value_2, 1,      r.src_view_name||'.'||r.u_name,
           'N','N' );
     end loop; 

 end app_appl_authorization;
 
 procedure app_auth_schemes(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id_0    number;
 v_parent_id_1    number;
 v_auth_name      varchar2(100) := '******';
 v_title          varchar2(1000) := ac_utils.G_AUTH_SCHEMES;
 v_src_1          clob;
 v_src_2          clob;
 v_is_src         varchar2(1);
 v_value_1        varchar2(4000);
 v_value_2        varchar2(4000);
 v_param_name     varchar2(4000);
 begin
  v_parent_id_0 := ac_utils.ins_result_msg(p_result_id,  pi_parent_id,  v_title, 1, true, ac_utils.G_AUTH_SCHEMES);
  ac_diff_not_in.app_auth_not_in(p_result_id, v_parent_id_0, pi_app_id1, pi_app_id2);
  ac_diff_not_in.app_auth_not_in(p_result_id, v_parent_id_0, pi_app_id2, pi_app_id1);
  for r in (with au1 as
              (
               select authentication_scheme_name, page_sentry_function_sr, pre_authentication_process_sr, 
                     session_verify_function_sr, post_authentication_process_sr, src_view_name, 
                     authentication_function_sr,
                     u_name, nvl(u_value,'*#*#*#') u_value
               from  VAC_APPLICATION_AUTH
               where application_id = pi_app_id1
              ),
               au2 as
              (
               select authentication_scheme_name, page_sentry_function_sr, pre_authentication_process_sr, 
                     session_verify_function_sr, post_authentication_process_sr, src_view_name, 
                     authentication_function_sr,
                     u_name, nvl(u_value,'*#*#*#') u_value
               from  VAC_APPLICATION_AUTH
               where application_id = pi_app_id2
              )
              select au1.authentication_scheme_name,
                     au1.u_name, 
                     au1.u_value u_value_1, 
                     au2.u_value u_value_2,  
                     au1.page_sentry_function_sr sf1, 
                     au2.page_sentry_function_sr sf2, 
                     au1.pre_authentication_process_sr pap1,
                     au1.pre_authentication_process_sr pap2,
                     au1.session_verify_function_sr svf1,
                     au2.session_verify_function_sr svf2,
                     au2.post_authentication_process_sr post_ap1,
                     au2.post_authentication_process_sr post_ap2,
                     au1.authentication_function_sr af_ap1,
                     au2.authentication_function_sr af_ap2,
                     au1.src_view_name
               from au1, au2
               where  (au1.authentication_scheme_name = au2.authentication_scheme_name)
                and   (au1.u_name = au2.u_name)
                and   (au1.u_value != au2.u_value)
               order by au1.authentication_scheme_name
          ) 
           loop
             if (v_auth_name != r.authentication_scheme_name) then
                v_parent_id_1 := ac_utils.ins_result_msg(p_result_id,  v_parent_id_0, r.authentication_scheme_name, 1, true, ac_utils.G_AUTH_SCHEMES );
                v_auth_name := r.authentication_scheme_name;
             end if;
             
             if (r.U_NAME = 'PAGE_SENTRY_FUNCTION_HS') then
               v_src_1      := r.sf1;
               v_src_2      := r.sf2;
               v_value_1    := substr(v_src_1,1,4000);
               v_value_2    := substr(v_src_2,1,4000);
               v_is_src     := 'Y';
               v_param_name := 'PAGE_SENTRY_FUNCTION';
             elsif  (r.U_NAME = 'SESSION_VERIFY_FUNCTION_HS') then
               v_src_1   := r.svf1;
               v_src_2   := r.svf2;
               v_value_1 := substr(v_src_1,1,4000);
               v_value_2 := substr(v_src_2,1,4000);
               v_is_src  := 'Y';
               v_param_name := 'SESSION_VERIFY_FUNCTION';
             elsif  (r.U_NAME = 'PRE_AUTHENTICATION_PROCESS_HS') then
               v_src_1   := r.pap1;
               v_src_2   := r.pap2;
               v_is_src  := 'Y';
               v_value_1 := substr(v_src_1,1,4000);
               v_value_2 := substr(v_src_2,1,4000);
               v_param_name := 'PRE_AUTHENTICATION_PROCESS';
             elsif  (r.U_NAME = 'AUTHENTICATION_FUNCTION_HS') then
               v_src_1   := r.af_ap1;
               v_src_2   := r.af_ap2;
               v_is_src  := 'Y';
               v_value_1 := substr(v_src_1,1,4000);
               v_value_2 := substr(v_src_2,1,4000);
               v_param_name := 'AUTHENTICATION_FUNCTION';
             else
               v_param_name :=  r.U_NAME;
               v_src_1   := null;
               v_src_2   := null;
               v_is_src  := 'N';
               v_value_1 := r.U_value_1;
               v_value_2 := r.U_value_2;
             end if;

             insert into ac_results
              (result_id,   parent_id,     param_name,   param_val_1, param_val_2 , 
               sort , help_text, object_type, 
               src_val_1,  src_val_2, is_branch, is_src)
             values
              (p_result_id, v_parent_id_1, v_param_name, v_value_1, v_value_2, 
               3,    r.src_view_name||'.'||r.u_name, ac_utils.G_AUTH_SCHEMES,
               v_src_1,    v_src_2, 'N', v_is_src  );
     
           end loop;
  
 end  app_auth_schemes;
 
 function app_page_da(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) return number is
 v_parent_id   number;
 v_da_name     varchar2(100) := '******';
 v_parent_id_1 number;
 v_sort        number := p_sort+1;
 begin
  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Dynamic Actions', p_sort, true, ac_utils.G_DYN_ACTIONS);
 
  ac_diff_not_in.app_page_dyn_actions_not_in(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id);
  ac_diff_not_in.app_page_dyn_actions_not_in(p_result_id, v_parent_id, pi_app_id2, pi_app_id1, pi_page_id);
  
  for r in(with da1 as 
            (select dynamic_action_name, u_name, u_value,
                    src_view_name
             from vac_application_page_da da
             where da.application_id = pi_app_id1
              and  da.page_id = pi_page_id
            ),
          da2 as 
            ( select  dynamic_action_name, u_name, u_value
              from vac_application_page_da da
              where da.application_id = pi_app_id2
               and  da.page_id = pi_page_id
            )
          select 
           da1.dynamic_action_name,
           da1.u_name, 
           da1.src_view_name,
           da1.u_value u_value_1,
           da2.u_value u_value_2
          from da1 join da2
           ON   (da1.u_name = da2.u_name)
            and (da1.dynamic_action_name = da2.dynamic_action_name)
            and ( -- select not equal values. ! here  null = null
                  (    (da1.u_value is not null or da2.u_value is not null) 
                   and lnnvl(da1.u_value=da2.u_value)
                  )
                )
           order by da1.dynamic_action_name
          ) loop
             if (v_da_name != r.dynamic_action_name) then
                v_parent_id_1 := ac_utils.ins_result_msg(p_result_id,  v_parent_id, r.dynamic_action_name , 1, true, ac_utils.G_DYN_ACTIONS );
                v_da_name := r.dynamic_action_name;
             end if;

             insert into ac_results
              (result_id,   parent_id,     param_name, param_val_1, param_val_2 , 
               sort , help_text, object_type, 
               is_branch, is_src)
             values
              (p_result_id, v_parent_id_1, r.U_NAME,   r.u_value_1 , r.u_value_2, 
               3,    r.src_view_name||'.'||r.u_name, ac_utils.G_DYN_ACTIONS,
               'N', 'N'  );

    end loop;
    
    return v_sort;
 end  app_page_da;

 function app_page_process(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) return number is
 v_parent_id     number;
 v_parent_id_1   number;
 v_src_1         clob;
 v_src_2         clob;
 v_is_src        varchar2(1);
 v_value_1       varchar2(4000);
 v_value_2       varchar2(4000);
 v_process_msg   varchar2(4000) := '******';
 v_u_name        varchar2(4000) := '******';
 begin
  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Page Process', p_sort, true, ac_utils.G_PAGE_PROCESS);
 
  ac_diff_not_in.app_page_process(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id);
  ac_diff_not_in.app_page_process(p_result_id, v_parent_id, pi_app_id2, pi_app_id1, pi_page_id);
  
  for r in ( with pp1 as
             (select nvl(u_value, '******') u_value,  u_name, process_sr,
                     src_view_name,
                     vp.process_msg 
              from vac_page_process vp
              where vp.application_id = pi_app_id1
                and vp.page_id = pi_page_id
              ),
              pp2  as
             (select nvl(u_value, '******') u_value,  u_name, process_sr,
                    vp.process_msg
              from vac_page_process vp
              where vp.application_id = pi_app_id2
                and vp.page_id = pi_page_id
              )
             select  pp1.u_name, 
                     pp1.u_value u_value_1, pp2.u_value u_value_2, 
                     pp1.process_sr process_sr_1,
                     pp2.process_sr process_sr_2,
                     pp1.src_view_name , pp1.process_msg
             from pp1 join pp2 ON (  (pp1.u_name = pp2.u_name)    
                                 and (pp1.process_msg  = pp2.process_msg)
                                 and (pp1.u_value != pp2.u_value)
                                  )
             order by pp1.process_msg
           ) loop
             if (v_process_msg != r.process_msg) then
                v_parent_id_1 := ac_utils.ins_result_msg(p_result_id,  v_parent_id, r.process_msg, 1, true, ac_utils.G_PAGE_PROCESS );
                v_process_msg := r.process_msg;
             end if;
             
             if (r.U_NAME = 'PROCESS_HS') then
               v_u_name  := 'PROCESS';  
             
               v_src_1   := r.process_sr_1;
               v_src_2   := r.process_sr_2;
               v_value_1 := substr(v_src_1,1,4000);
               v_value_2 := substr(v_src_2,1,4000);

               v_is_src  := 'Y';
             else
               v_u_name  := r.U_NAME;  
               v_src_1   := null;
               v_src_2   := null;
               v_value_1 := r.U_value_1;
               v_value_2 := r.U_value_2;

               v_is_src  := 'N';
             end if;

             insert into ac_results
              (result_id,   parent_id,     param_name, param_val_1, param_val_2 , 
               sort , help_text, object_type, 
               src_val_1,  src_val_2, is_branch, is_src)
             values
              (p_result_id, v_parent_id_1, v_U_NAME,     v_value_1, v_value_2, 
               3,    r.src_view_name||'.'||r.u_name, null ,
               v_src_1,    v_src_2, 'N', v_is_src  );

  end loop;
  
  return p_sort + 1;
 end  app_page_process;  
 
 procedure app_pages(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) is
   v_parent_id number;
   v_sort      number := p_sort;
   v_num_pages number := 1;
   begin
    app_apex.setMsgValue('Pages...'); 

    for r in (  select app1.page_id  
                  from apex_application_pages app1 join 
                       apex_application_pages app2 ON (app1.page_id = app2.page_id) 
                  where app1.application_id = pi_app_id1
                    and app2.application_id = pi_app_id2
                    and ((pi_page_id is null) or
                         (app1.page_id = pi_page_id)
                        )
             ) loop
              app_apex.setMsgValue(p_result_id, '#Pages '||v_num_pages||' done...'); 
 
              v_sort      := v_sort + 1;
              v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Page id='||r.page_id, v_sort, true, ac_utils.G_PAGE );
              v_sort      := app_page_parameters (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, r.page_id, v_sort);
              v_sort      := app_regions         (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, r.page_id, v_sort);
              v_sort      := app_page_items      (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, r.page_id, v_sort);
              v_sort      := app_page_buttons    (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, r.page_id, v_sort);
              v_sort      := app_page_branches   (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, r.page_id, v_sort);
              v_sort      := app_page_validations(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, r.page_id, v_sort);
              v_sort      := app_page_da         (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, r.page_id, v_sort);
              v_sort      := app_ir_regions      (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, r.page_id, v_sort);
              v_sort      := app_page_process    (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, r.page_id, v_sort);

              v_num_pages := v_num_pages + 1;

              if app_apex.isCancel()  then
                app_apex.setMsgValue(p_result_id,'Canceled by user...');
                exit;
              end if;

    end loop;
 end app_pages;

 procedure app_comp(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id   number;
 v_parent_id_1 number;
 v_computation_item varchar2(1000) := '*****';
 begin
    v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Application Computations' , p_sort, true, ac_utils.G_COMPUTATION);
    
    ac_diff_not_in.app_comp_not_in(p_result_id, v_parent_id, pi_app_id1, pi_app_id2); 
    ac_diff_not_in.app_comp_not_in(p_result_id, v_parent_id, pi_app_id2, pi_app_id1); 

    for r in ( 
       with app_comp1 as (
                 select computation_item, u_name, u_value,
                        ap1.src_view_name
                  from vac_application_computations  ap1 
                 where ap1.application_id = pi_app_id1
                  ),
            app_comp2 as (
                 select computation_item, u_name, u_value
                  from vac_application_computations  ap1 
                 where ap1.application_id = pi_app_id2
                  )
        select p1.computation_item, p_result_id, v_parent_id, p1.u_name, 
               p1.u_value u_value_1, p2.u_value u_value_2,  
               p1.src_view_name||'.'||p1.u_name help_text            
        from app_comp1 p1 join app_comp2 p2
        ON (  
             (p1.computation_item = p2.computation_item) 
         and (p1.u_name = p2.u_name)    
         and (nvl(p1.u_value,'#$#$#$#') != nvl(p2.u_value,'#$#$#$#'))   
           )
        order by p1.computation_item
     ) loop 
      if r.computation_item != v_computation_item then
       v_parent_id_1      := ac_utils.ins_result_msg(p_result_id,  v_parent_id, 'Computation: #'||r.COMPUTATION_ITEM , 1);
       v_computation_item := r.computation_item;
      end if;
               
      insert into ac_results
       (result_id,      parent_id,     param_name,    param_val_1,  param_val_2 , sort, help_text, is_branch, is_src)
      values
       (p_result_id,    v_parent_id_1, r.u_name,      r.U_value_1,  r.U_value_2,     1, r.help_text, 'N',     'N') ;
     end loop;
 end  app_comp;

 procedure app_app_proc(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
  v_parent_id number;
  v_parent_process_id number;
  v_process_name  varchar2(100) := '###***';
  v_num_res       number := 0;
  v_src_1         clob;
  v_src_2         clob;
  v_is_src        varchar2(1) := 'N';
  v_value_1       varchar2(4000);
  v_value_2       varchar2(4000);
  v_param_name    varchar2(4000);
   
 begin
     v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Application Processes' , p_sort, true, ac_utils.G_PROCESS);
     ac_diff_not_in.app_proc_not_in(p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
     ac_diff_not_in.app_proc_not_in(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);
     
     for r in (       
               with app_comp1 as (
                 select process_name, u_name, u_value, process_sr,
                  src_view_name 
                  from vac_application_processes  ap1 
                 where ap1.application_id = pi_app_id1
               ),
                    app_comp2 as (
                 select process_name, u_name, u_value, process_sr 
                  from vac_application_processes  ap1 
                 where ap1.application_id = pi_app_id2
               )
        select p1.process_name, 
               p1.u_name,    
               p1.u_value u_value_1,  
               p2.u_value u_value_2,
               p1.process_sr  process_src_1, 
               p2.process_sr  process_src_2,
               p1.src_view_name       
        from app_comp1 p1 join app_comp2 p2
        ON (  
             (p1.process_name = p2.process_name) 
         and (p1.u_name = p2.u_name)    
         and (nvl(p1.u_value,'#$#$#$#') != nvl(p2.u_value,'#$#$#$#'))   
           )
           order by p1.process_name, p1.u_name
     ) loop
       if (v_process_name != r.process_name) then
         v_process_name := r.process_name;
         v_parent_process_id := ac_utils.ins_result_msg(p_result_id,  v_parent_id, 'Process:'|| r.process_name, 3, true, ac_utils.G_PROCESS);
       end if;
       if upper(r.u_name) = 'PROCESS_HS' then
         v_src_1  := r.process_src_1;
         v_src_2  := r.process_src_2;
         v_value_1:= substr(v_src_1,1,4000);
         v_value_2:= substr(v_src_2,1,4000);
         v_param_name := 'PROCESS';
         v_is_src := 'Y';
       else
         v_is_src := 'N';
         v_value_1 := r.U_value_1;
         v_value_2 := r.U_value_2;
         v_param_name := upper(r.u_name);
       end if;
       insert into ac_results
       (result_id,      parent_id,           param_name, param_val_1, param_val_2, sort,
       help_text, 
       src_val_1, src_val_2, is_branch, is_src)
       values
       (p_result_id,    v_parent_process_id, v_param_name,     v_value_1, v_value_2,  v_num_res,
        r.src_view_name||'.'||v_param_name,
        v_src_1, v_src_2, 'N', v_is_src 
        );
       v_num_res := v_num_res+1;
     end loop;
 end;

 procedure remove_empty_branches(p_report_id number) is
 begin
   for i in 1..100 loop -- just in case number of cycles is limited
    delete from ac_results r
    where r.result_id = p_report_id
      and r.is_branch = 'Y'
      and not exists
      ( -- branch is empty
        select 1 from ac_results r1
         where r1.result_id = p_report_id
           and r1.parent_id = r.id
      );
     exit when sql%rowcount = 0;
    end loop;
 end  remove_empty_branches;

 function getRoot(p_res_id number) return number is
   v_root_id number; 
   begin
    select r.id into v_root_id
    from  ac_results r
    where r.result_id = p_res_id
     and  r.parent_id is null;
     
     return  v_root_id;    
   end  getRoot;

 function app_diff_init(pi_app_id1 number, pi_app_id2 number) return number is
 v_res_id    number;
 v_parent_id number;
 begin
    v_res_id    := Create_Header(p_app_id_1 => pi_app_id1,p_app_id_2 => pi_app_id2);
    v_parent_id := APP_REC(v_res_id, pi_app_id1, pi_app_id2, 1);
    
    return v_res_id ;
 end  app_diff_init;

 procedure  app_diff_pages(pi_app_id1 number, pi_app_id2 number, p_res_id number, pi_page_id number) is
 v_res_id     number;
 v_parent_id  number;
 v_date_start timestamp := systimestamp;
 v_interv     interval day to second;
 v_user_msg   varchar2(100) := 'Done...';
 begin
   
   app_apex.setCancel(p_isCancel => 'N');
   app_apex.setMsgValue('Started...'); 
 
  -- logger.log(p_text => 'Started...',p_scope => 'ac_diff.app_diff', p_extra => 'P50_DO_CANCEL='|| v('P50_DO_CANCEL'));
 
   v_res_id := p_res_id;
   v_parent_id := getRoot(v_res_id);

   app_pages(v_res_id, v_parent_id, pi_app_id1,  pi_app_id2, pi_page_id,   8);

   app_apex.setMsgValue('Preparing results...');
   remove_empty_branches(v_res_id);   

   v_interv  := systimestamp - v_date_start;

   if app_apex.isCancel() then
     v_user_msg := 'Cancelled by user...';
   else
     v_user_msg := 'Done...';
   end if;
   app_apex.setMsgValue(v_user_msg || EXTRACT(MINUTE from v_interv)
                                   || 'min '|| EXTRACT(SECOND from v_interv)
                                   || 'sec' ); 

   -- logger.log(p_text => 'Done...xx',p_scope => 'ac_diff.app_diff', p_extra => 'P50_DO_CANCEL='|| v('P50_DO_CANCEL'));

 end app_diff_pages;


end ac_diff;
/
